<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    
<%@page import="support.*, java.util.*, java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Applications</title>
</head>
<body>

<%

	String disciplineParam = request.getParameter("discipline");
	String disciplineIDParam = request.getParameter("disciplineID");
	String specializationParam = request.getParameter("specialization");
	String specializationIDParam = request.getParameter("specializationID");
	
	
	Class.forName("org.postgresql.Driver");

	Connection connect = DriverManager.getConnection("jdbc:postgresql://localhost/tester?user=postgres&password=admin");
	Statement stmt = connect.createStatement();
	ResultSet finalRset = null;
	
	
	
	
	if(disciplineParam != null)
	{
		Statement prepStmt = connect.createStatement();
		String query = "SELECT students.lastname, students.firstname, students.middleinitial,"
		+ " degrees.student	FROM public.students, public.degrees WHERE degrees.discipline="+ disciplineIDParam 
		+ " AND students.id = degrees.student ORDER BY students.lastname ASC;";
		ResultSet epic = prepStmt.executeQuery(query);
		String student2 = "";
	
	%>
		
		Applicants with degrees in <%= disciplineParam %><br><br>
	
		<%
		while (epic.next())
		{
			student2 = epic.getString("lastname") + ", " + epic.getString("firstname") + epic.getString("middleinitial") + ".";
			%><%= student2 %><br><%
	 	}
	}
	
	else if(specializationParam != null)
	{
		Statement stmt2 = connect.createStatement();
		
		ResultSet interSet;
		
		String specID = specializationIDParam;
		%>
		
		Applicants who wish to specialize in <%= specializationParam %><br><br>
		
		<%
		finalRset = stmt2.executeQuery("SELECT * FROM students WHERE specialization=" + specID + " ORDER BY lastname");
	}
	else
	{
	%>
	
	All applicants<br><br>
		
		<%
		finalRset = stmt.executeQuery("SELECT * FROM students ORDER BY lastname");
	}
	
	if(disciplineParam == null)
	{
		
		String student = "";
		
		while( finalRset.next() )
		{
			student = finalRset.getString("lastname") + ", " + finalRset.getString("firstname") + " " + finalRset.getString("middleinitial") + ".";
			%>
			<%=student %><br>
			<%	
		}
				
	}
	
	%>

</body>
</html>